package dao.Impl;

import dao.AdoptDao;
import entity.AdoptAnimal;
import handler.AdoptListHandler;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import utils.C3p0Helper;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.List;

public class AdoptDaoImpl implements AdoptDao {

    @Override
    public List<AdoptAnimal> findAllPending() {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "SELECT a.id,a.state, " +
                "u.id AS 'user.id', " +
                "u.userName AS 'user.userName', " +
                "p.id AS 'pet.id', " +
                "p.petName AS 'pet.petName', " +
                "p.state AS 'pet.state', " +
                "a.adoptTime " +
                "FROM adoptanimal a " +
                "JOIN user u ON a.userId = u.id " +
                "JOIN pet p ON a.petId = p.id";
        ResultSetHandler<List<AdoptAnimal>> handler = new AdoptListHandler();
        try {
            return qr.query(sql,handler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<AdoptAnimal> findByAdoptTime(LocalDate adoptTime) {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "SELECT \n" +
                "    a.id,\n" +
                "    a.state,\n" +
                "    u.id AS \"user.id\",\n" +
                "    u.userName AS \"user.userName\",\n" +
                "    p.id AS \"pet.id\",\n" +
                "    p.petName AS \"pet.petName\",\n" +
                "    p.state AS \"pet.state\",\n" +
                "    a.adoptTime\n" +
                "FROM \n" +
                "    adoptAnimal a\n" +
                "JOIN \n" +
                "    user u ON a.userId = u.id\n" +
                "JOIN \n" +
                "    pet p ON a.petId = p.id\n" +
                "WHERE \n" +
                "    a.adoptTime = ?";
        ResultSetHandler<List<AdoptAnimal>> handler = new AdoptListHandler();
        try {
            return qr.query(sql,handler,adoptTime);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean agreeAdopt(int id) {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "UPDATE adoptanimal SET state = 1 WHERE id = ?";
        try {
            qr.update(sql, id);
            return true;
        } catch (SQLException e) {
            return false;
            //throw new RuntimeException(e);

        }
    }

    @Override
    public boolean disagreeAdopt(int id) {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "UPDATE adoptanimal SET state = 2 WHERE id = ?";
        try {
            qr.update(sql, id);
            return true;
        } catch (SQLException e) {
            return false;
//            throw new RuntimeException(e);
        }
    }

    @Override
    public List<AdoptAnimal> findAgreeAll() {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "SELECT a.id,a.state, \n" +
                "  u.id AS 'user.id', \n" +
                "   u.userName AS 'user.userName',\n" +
                "  p.id AS 'pet.id', \n" +
                "  p.petName AS 'pet.petName', \n" +
                "   p.state AS 'pet.state', \n" +
                "  a.adoptTime \n" +
                " FROM adoptanimal a \n" +
                " JOIN user u ON a.id = u.id \n" +
                "JOIN pet p ON a.id = p.id\n" +
                "WHERE a.state=1";
        ResultSetHandler<List<AdoptAnimal>> handler = new AdoptListHandler();
        try {
            return qr.query(sql,handler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<AdoptAnimal> findDisagreeAll() {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "SELECT a.id,a.state, \n" +
                "  u.id AS 'user.id', \n" +
                "   u.userName AS 'user.userName',\n" +
                "  p.id AS 'pet.id', \n" +
                "  p.petName AS 'pet.petName', \n" +
                "   p.state AS 'pet.state', \n" +
                "  a.adoptTime \n" +
                " FROM adoptanimal a \n" +
                " JOIN user u ON a.id = u.id \n" +
                "JOIN pet p ON a.id = p.id\n" +
                "WHERE a.state=2";
        ResultSetHandler<List<AdoptAnimal>> handler = new AdoptListHandler();
        try {
            return qr.query(sql,handler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<AdoptAnimal> finfByUsername(String username) {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql="SELECT a.id,a.state, \n" +
                "   u.id AS 'user.id', \n" +
                " u.userName AS 'user.userName',\n" +
                "  p.id AS 'pet.id', \n" +
                "  p.petName AS 'pet.petName', \n" +
                "  p.state AS 'pet.state', \n" +
                "  a.adoptTime \n" +
                " FROM adoptanimal a \n" +
                " JOIN user u ON a.id = u.id \n" +
                " JOIN pet p ON a.id = p.id\n" +
                "WHERE u.userName=?";
        ResultSetHandler<List<AdoptAnimal>> handler = new AdoptListHandler();
        try {
            return qr.query(sql,handler,username);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void save(AdoptAnimal adoptAnimal) {
        QueryRunner qr = new QueryRunner(C3p0Helper.getDataSource());
        String sql = "INSERT INTO adoptanimal (userId, petId, adoptTime, state) VALUES (?, ?, ?, ?)";
        try {
            qr.update(sql, adoptAnimal.getUser().getId(), adoptAnimal.getPet().getId(), adoptAnimal.getAdoptTime(), adoptAnimal.getState());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}